package dtt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
//import java.util.Iterator;

public class ProjectBO {
	@SuppressWarnings("unchecked")
	public ArrayList getProjects() {
		// ProjectBean project = new ProjectBean();
		ArrayList projectBeanList = new ArrayList();
		String template = "select t_project.projectId, projectNbr, projectDesc, projectVernbr, projectCategory from t_project";
		try {
			Connection con = DBConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(template);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ProjectBean bean = new ProjectBean();
				bean.setProjectId(rs.getInt(1));
				bean.setProjectNbr(rs.getString(2));
				bean.setProjectDesc(rs.getString(3));
				bean.setProjectVernbr(rs.getString(4));
				bean.setProjectCategory(rs.getString(5));

				projectBeanList.add(bean);
			}// end while
			ps.close();
			con = null;
		}// end try
		catch (SQLException e) {
			e.printStackTrace();
		}
		return projectBeanList;
	}
	
	
	@SuppressWarnings("unchecked")
	public ArrayList getAssignedProjects(int userId) {
		// ProjectBean project = new ProjectBean();
		ArrayList projectBeanList = new ArrayList();
		String template = "select t_project.projectId, projectNbr, projectDesc, projectVernbr, projectCategory from t_project, t_role where t_role.userId = ? and t_role.projectId = t_project.projectId";
		try {
			Connection con = DBConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(template);
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ProjectBean bean = new ProjectBean();
				bean.setProjectId(rs.getInt(1));
				bean.setProjectNbr(rs.getString(2));
				bean.setProjectDesc(rs.getString(3));
				bean.setProjectVernbr(rs.getString(4));
				bean.setProjectCategory(rs.getString(5));

				projectBeanList.add(bean);
			}// end while
			ps.close();
			con = null;
		}// end try
		catch (SQLException e) {
			e.printStackTrace();
		}
		return projectBeanList;
	}
	
	public ArrayList getAssignedProjects(String userId) {
		// ProjectBean project = new ProjectBean();
		ArrayList projectBeanList = new ArrayList();
		String template = "select t_project.projectId, projectNbr, projectDesc, projectVernbr, projectCategory from t_project, t_role,t_user where t_user.userName = ? and t_role.userId = t_user.userId and t_role.projectId = t_project.projectId";
		try {
			Connection con = DBConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(template);
			ps.setString(1, userId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ProjectBean bean = new ProjectBean();
				bean.setProjectId(rs.getInt(1));
				bean.setProjectNbr(rs.getString(2));
				bean.setProjectDesc(rs.getString(3));
				bean.setProjectVernbr(rs.getString(4));
				bean.setProjectCategory(rs.getString(5));
			//	projectBeanList.add(bean); warning here by Hongtao
			}// end while
			ps.close();
			con = null;
		}// end try
		catch (SQLException e) {
			e.printStackTrace();
		}
		return projectBeanList;
	}	
	public ProjectBean getProjectDetails(String projectNbr) {
		ProjectBean bean = null;
		String template = "select projectId, projectNbr, projectDesc, projectvernbr, projectCategory from t_project where projectId = ?";
		try {
			Connection con = DBConnection.getConnection();
			PreparedStatement ps = con.prepareStatement(template);
			ps.setInt(1, Integer.parseInt(projectNbr));
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				bean = new ProjectBean();
				bean.setProjectId(rs.getInt(1));
				bean.setProjectNbr(rs.getString(2));
				bean.setProjectDesc(rs.getString(3));
				bean.setProjectVernbr(rs.getString(4));
				bean.setProjectCategory(rs.getString(5));

			}// end while
			ps.close();
			con = null;
		}// end try
		catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}
	/**
	 * When a new defect is added,this method generates a new defectId based on number of defect already
	 * exsisiting in the given project
	 * @return
	 */
	public String generateProjectId(){
		String projectId;
		int count = 0;
		Connection con1 = DBConnection.getConnection();
		String template = "select count(*) from t_project";
		try{
			PreparedStatement ps1 = con1.prepareStatement(template);
			
			ResultSet rs1 = ps1.executeQuery();
			rs1.next();
			count = rs1.getInt(1);
			} //end try
		catch(SQLException e){
		 e.printStackTrace();
		}// end catch
		count +=1;
		projectId = "" + count; 
		
		return projectId;
	}//end method generateDefectId
	public boolean addProject(ProjectBean project)
	{
//		DefectBean outputbean = null;
		boolean flag = false;
		Connection con = DBConnection.getConnection();
		String template ="insert into t_project (projectId, projectNbr, projectDesc, projectvernbr, projectCategory)values" +
				                         "(?,?,?,?,?)";
		try {
		PreparedStatement ps = con.prepareStatement(template);
		String projectId= generateProjectId();
		//String defectId = generateDefectId(projectId);
				
		ps.setInt(1,Integer.parseInt(projectId));
		project.setProjectId(Integer.parseInt(projectId));
		ps.setString(2, project.getProjectNbr());
		ps.setString(3, project.getProjectDesc());
		ps.setString(4, project.getProjectVernbr());
		ps.setString(5, project.getProjectCategory());
				
		int count = ps.executeUpdate();
		ps.close();
		
		
		DBConnection.releaseConnection(con);
		con = null;
		
		if(count>0){
			flag = true;
		}
		else{
			flag = false;
		}
		}
		catch(SQLException e){
		 System.out.println("WRONG HERE");
		 e.printStackTrace();
		}
		System.out.print("Project number is "+project.getProjectId());
					
		return flag;
		
	}
	public boolean updateProject(ProjectBean project)
	{
//		DefectBean outputbean = null;
		boolean flag = false;
		Connection con = DBConnection.getConnection();
		String template ="update t_project set projectDesc =?, projectvernbr=?, projectCategory=? ,projectNbr=? where projectId=?";
		try {
		PreparedStatement ps = con.prepareStatement(template);				
		ps.setString(1, project.getProjectDesc());
		ps.setString(2, project.getProjectVernbr());
		ps.setString(3, project.getProjectCategory());
		ps.setString(4, project.getProjectNbr());
		ps.setInt(5, project.getProjectId());
		
		System.out.println("project update:" + project.getProjectId() + "-" + project.getProjectNbr());		
		int count = ps.executeUpdate();
		ps.close();
		
		
		DBConnection.releaseConnection(con);
		con = null;
		
		if(count>0){
			flag = true;
		}
		else{
			flag = false;
		}
		}
		catch(SQLException e){
		 System.out.println("WRONG HERE");
		 e.printStackTrace();
		}
		System.out.print("Project number is "+project.getProjectId());
					
		return flag;
		
	}
		
}
